using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;

namespace PCSComPayroll.DS
{
	public class PAY_EstimateMasterDS
	{
		public PAY_EstimateMasterDS()
		{
		}

		private const string THIS = "PCSComPayroll.DS.DS.PAY_EstimateMasterDS";
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PAY_EstimateMasterVO objObject = (PAY_EstimateMasterVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO PAY_EstimateMaster("
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD + ")"
					+ "VALUES(?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PAY_EstimateMasterTable.TRANSDATE_FLD].Value = objObject.TransDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSNO_FLD, OleDbType.WChar));
				ocmdPCS.Parameters[PAY_EstimateMasterTable.TRANSNO_FLD].Value = objObject.TransNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_EstimateMasterTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public int AddAndReturnID(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".AddAndReturnID()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PAY_EstimateMasterVO objObject = (PAY_EstimateMasterVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO PAY_EstimateMaster("
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + ","
					+ PAY_EstimateMasterTable.COMPLETED_FLD + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD + ")"
					+ "VALUES(?,?,?,?)"
					+ "; SELECT @@IDENTITY as LatestID";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSDATE_FLD, OleDbType.Date)).Value = objObject.TransDate;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSNO_FLD, OleDbType.WChar)).Value = objObject.TransNo;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.COMPLETED_FLD, OleDbType.Boolean)).Value = objObject.Completed;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer)).Value = objObject.SaleOrderDetailID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				return Convert.ToInt32(ocmdPCS.ExecuteScalar());
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "DELETE " + PAY_EstimateMasterTable.TABLE_NAME + " WHERE  " + "EstimateMasterID" + "=" + pintID.ToString();
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void DeleteDetail(int pintID)
		{
			const string METHOD_NAME = THIS + ".DeleteDetail()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "DELETE " + PAY_EstimateDetailTable.TABLE_NAME + " WHERE  " + "EstimateMasterID" + "=" + pintID.ToString();
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			
			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + ","
					+ PAY_EstimateMasterTable.COMPLETED_FLD + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD
					+ " FROM " + PAY_EstimateMasterTable.TABLE_NAME
					+ " WHERE " + PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PAY_EstimateMasterVO objObject = new PAY_EstimateMasterVO();

				while (odrPCS.Read())
				{
					objObject.EstimateMasterID = int.Parse(odrPCS[PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD].ToString());
					objObject.TransDate = DateTime.Parse(odrPCS[PAY_EstimateMasterTable.TRANSDATE_FLD].ToString());
					objObject.TransNo = odrPCS[PAY_EstimateMasterTable.TRANSNO_FLD].ToString();
					objObject.SaleOrderDetailID = int.Parse(odrPCS[PAY_EstimateMasterTable.SALEORDERDETAILID_FLD].ToString());
					try
					{
						objObject.Completed = Convert.ToBoolean(odrPCS[PAY_EstimateMasterTable.COMPLETED_FLD]);
					}
					catch{}

				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PAY_EstimateMasterVO objObject = (PAY_EstimateMasterVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE PAY_EstimateMaster SET "
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + "=   ?" + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + "=   ?" + ","
					+ PAY_EstimateMasterTable.COMPLETED_FLD + "=  ?" + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD + "=  ?"
					+ " WHERE " + PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSDATE_FLD, OleDbType.Date)).Value = objObject.TransDate;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.TRANSNO_FLD, OleDbType.WChar)).Value = objObject.TransNo;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.COMPLETED_FLD, OleDbType.Boolean)).Value = objObject.Completed;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer)).Value = objObject.SaleOrderDetailID;
				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD, OleDbType.Integer)).Value = objObject.EstimateMasterID;

				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + ","
					+ PAY_EstimateMasterTable.COMPLETED_FLD + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD
					+ " FROM " + PAY_EstimateMasterTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_EstimateMasterTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ PAY_EstimateMasterTable.ESTIMATEMASTERID_FLD + ","
					+ PAY_EstimateMasterTable.TRANSDATE_FLD + ","
					+ PAY_EstimateMasterTable.TRANSNO_FLD + ","
					+ PAY_EstimateMasterTable.SALEORDERDETAILID_FLD;


				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PAY_EstimateMasterTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}

		/// <summary>
		/// Check for exist F.Goods Code in payment request master table
		/// </summary>
		/// <param name="pintSaleOrderDetailID">Sale order detail</param>
		/// <returns></returns>
		public bool IsExistFGoodsCode(int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".IsExist()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT " + PAY_PaymentRequestMasterTable.SALEORDERDETAILID_FLD
					+ " FROM " + PAY_PaymentRequestMasterTable.TABLE_NAME
					+ " WHERE " + PAY_PaymentRequestMasterTable.SALEORDERDETAILID_FLD + "=" + pintSaleOrderDetailID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				DataTable dtbData = new DataTable();
				odadPCS.Fill(dtbData);
				return dtbData.Rows.Count > 0;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
		/// <summary>
		/// Check for exist ProductionLine in master table
		/// </summary>
		/// <param name="pintSaleOrderDetailID">Estimate Master ID</param>
		/// <param name="pintProductionLineID">Production Line ID</param>
		/// <returns></returns>
		public bool IsExistProductionLine(int pintSaleOrderDetailID, int pintProductionLineID)
		{
			const string METHOD_NAME = THIS + ".IsExistProductionLine()";
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT " + PAY_PaymentRequestMasterTable.PRODUCTIONLINEID_FLD
					+ " FROM " + PAY_PaymentRequestMasterTable.TABLE_NAME
					+ " WHERE " + PAY_PaymentRequestMasterTable.SALEORDERDETAILID_FLD + "=" + pintSaleOrderDetailID
					+ " AND " + PAY_PaymentRequestMasterTable.PRODUCTIONLINEID_FLD + "=" + pintProductionLineID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				DataTable dtbData = new DataTable();
				odadPCS.Fill(dtbData);
				return dtbData.Rows.Count > 0;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}